/*
 * Copyright 2016 atnoce.com
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.atnoce.db;

import com.atnoce.pojo.PasswordItem;
import com.atnoce.util.CommonUtil;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;


import javafx.collections.FXCollections;
import javafx.collections.ObservableList;

public class PasswordDao {
	private Statement statement=null;
	private void initStatement() throws SQLException, ClassNotFoundException{
		if(statement==null){
			statement=DbUtils.getDBConnection().createStatement();
		}
	}
        /**
         * 通过分类名称获取密码条目
         * @param dirName
         * @return
         * @throws SQLException 
         */
	public ObservableList<PasswordItem> getPasswordsFormDirName(String dirName) throws SQLException, ClassNotFoundException{
            PreparedStatement ps=DbUtils.getDBConnection().prepareStatement("select * from fenlei where dirName=?;");
            ps.setString(1, dirName);
            ResultSet executeQuery = ps.executeQuery();
            String dirId = null;
            if(executeQuery.next()){
                dirId=executeQuery.getString("dirId");
            }
            executeQuery.close();
            ps.close();
            if(dirId!=null){
                ps=DbUtils.getDBConnection().prepareStatement("select * from passitem where dirTypeId=?;");
		ps.setString(1, dirId);

		ResultSet query = ps.executeQuery();
		ObservableList<PasswordItem> oblist=FXCollections.observableArrayList();
		while(query.next()){
			String id=query.getString("passId");
			String account=query.getString("account");
			String pass=query.getString("passwordStr");
			String remarkStr=query.getString("remarkStr");
			String createTime=query.getString("createTime");
			String isDelete=query.getString("isDelete");
			String dirTypeId=query.getString("dirTypeId");
			oblist.add(new PasswordItem(id,account, pass, remarkStr, dirTypeId,
					LocalDate.parse(createTime,CommonUtil.getDateTimeFormatter()), isDelete));
		}

		return oblist;
            }
		return null;
	}
        /**
         * 添加密码条目
         * @param passwordItem
         * @param dirName
         * @return
         * @throws SQLException 
         */
	public boolean addPasswordItem(PasswordItem passwordItem,String dirName) throws SQLException, ClassNotFoundException{
                PreparedStatement ps=DbUtils.getDBConnection().prepareStatement("select * from fenlei where dirName=?;");
                ps.setString(1, dirName);
            ResultSet executeQuery = ps.executeQuery();
            String dirId = null;
            if(executeQuery.next()){
                dirId=executeQuery.getString("dirId");
            }
            ps.close();
            if(dirId!=null){
                ps=DbUtils.getDBConnection().prepareStatement("insert into passitem values (?,?,?,?,?,?,?);");
		ps.setString(1, passwordItem.getPassId());
		ps.setString(2, passwordItem.getAccount());
		ps.setString(3, passwordItem.getPasswordStr());
		ps.setString(4, passwordItem.getRemarkStr());
		ps.setString(5, passwordItem.getCreateTime().toString());
		ps.setString(6, passwordItem.getIsDelete());
		ps.setString(7, dirId);
		ps.execute();

		int updateCount = ps.getUpdateCount();
		if(updateCount==1){
                    CommonUtil.updateSyncState();
                    return true;
                }
                return false;
            } 
            return false;
	}
        /**
         * 删除密码条目
         * @param passwordItem
         * @return
         * @throws SQLException 
         */
        public boolean deletePasswordItem(PasswordItem passwordItem) throws SQLException, ClassNotFoundException{
            PreparedStatement preparedStatement=DbUtils.getDBConnection().prepareStatement("delete from passitem where passId=?;");
            preparedStatement.setString(1, passwordItem.getPassId());
            preparedStatement.execute();
            int updateCount = preparedStatement.getUpdateCount();
            if(updateCount==1){
                    CommonUtil.updateSyncState();
                    return true;
                }
                return false;
        }
        /**
         * 通过分类ID删除密码项
         * @param dirName
         * @throws SQLException 
         */
        public void deletePasswordItemFromDirType(String dirName) throws SQLException, ClassNotFoundException{
            PreparedStatement ps=DbUtils.getDBConnection().prepareStatement("select * from fenlei where dirName=?;");
                ps.setString(1, dirName);
            ResultSet executeQuery = ps.executeQuery();
            String dirId = null;
            if(executeQuery.next()){
                dirId=executeQuery.getString("dirId");
            }
            ps.close();
            if(dirId!=null){
                ps=DbUtils.getDBConnection().prepareStatement("delete from passitem where dirTypeId=?;");
                ps.setString(1, dirId);
                ps.execute();
                ps.close();
                CommonUtil.updateSyncState();
            }
            
        }
        /**
         * 更新密码项
         * @param passwordItem
         * @param dirName
         * @return
         * @throws SQLException 
         */
        public boolean updatePasswordItem(PasswordItem passwordItem,String dirName) throws SQLException, ClassNotFoundException{
             PreparedStatement ps=DbUtils.getDBConnection().prepareStatement("select * from fenlei where dirName=?;");
                ps.setString(1, dirName);
            ResultSet executeQuery = ps.executeQuery();
            String dirId = null;
            if(executeQuery.next()){
                dirId=executeQuery.getString("dirId");
            }
            ps.close();
            if(dirId!=null){
                ps=DbUtils.getDBConnection().prepareStatement("update passitem set account=?,passwordStr=?,remarkStr=?,dirTypeId=? where passId=?;");
                ps.setString(1, passwordItem.getAccount());
                ps.setString(2, passwordItem.getPasswordStr());
                ps.setString(3, passwordItem.getRemarkStr());
                ps.setString(4, passwordItem.getDirTypeId());
                ps.setString(5, passwordItem.getPassId());

                ps.execute();
                int updateCount = ps.getUpdateCount();
                ps.close();
                if(updateCount==1){
                    CommonUtil.updateSyncState();
                    return true;
                }
                return false;
            }
            return false;
            
        }
        /**
         * 通过密码项的ID获取密码项对象
         * @param passId
         * @return 
         * @throws java.sql.SQLException 
         */
        public PasswordItem getPasswordItemFromPassId(String passId) throws SQLException, ClassNotFoundException{
            PreparedStatement ps=DbUtils.getDBConnection().prepareStatement("select * from passitem where passId=?;");
            ps.setString(1, passId);
            ResultSet executeQuery = ps.executeQuery();
            if(executeQuery.next()){
                String id=executeQuery.getString("passId");
		String account=executeQuery.getString("account");
		String pass=executeQuery.getString("passwordStr");
		String remarkStr=executeQuery.getString("remarkStr");
		String createTime=executeQuery.getString("createTime");
		String isDelete=executeQuery.getString("isDelete");
		String dirTypeId=executeQuery.getString("dirTypeId");
                return new PasswordItem(id,account, pass, remarkStr, dirTypeId,
					LocalDate.parse(createTime,CommonUtil.getDateTimeFormatter()), isDelete);
            }
            return null;
        }
        /**
         * 获取所有密码项
         * @return
         * @throws SQLException 
         */
        public List<PasswordItem> getAllPasswordItem() throws SQLException, ClassNotFoundException{
            List<PasswordItem> list=new ArrayList<>();
            initStatement();
            PreparedStatement ps=DbUtils.getDBConnection().prepareStatement("select * from passitem");
           ResultSet executeQuery= ps.executeQuery();
            //ResultSet executeQuery = statement.executeQuery("select * from passitem");
            PasswordItem pi;
            while(executeQuery.next()){
                pi=new PasswordItem();
                pi.setPassId(executeQuery.getString("passId"));
                pi.setAccount(executeQuery.getString("account"));
                pi.setPasswordStr(executeQuery.getString("passwordStr"));
                pi.setRemarkStr(executeQuery.getString("remarkStr"));
                pi.setCreateTime(LocalDate.parse(executeQuery.getString("createTime"),CommonUtil.getDateTimeFormatter()));
                pi.setIsDelete(executeQuery.getString("isDelete"));
                pi.setDirTypeId(executeQuery.getString("dirTypeId"));
                
                list.add(pi);
            }
            return list;
        }
        public boolean updatePasswordItemFromId(PasswordItem passwordItem) throws SQLException, ClassNotFoundException{
            PreparedStatement ps=DbUtils.getDBConnection().prepareStatement("update passitem set account=?,passwordStr=?,remarkStr=?,dirTypeId=? where passId=?;");
            ps.setString(1, passwordItem.getAccount());
            ps.setString(2, passwordItem.getPasswordStr());
            ps.setString(3, passwordItem.getRemarkStr());
            ps.setString(4, passwordItem.getDirTypeId());
            ps.setString(5, passwordItem.getPassId());
            int executeUpdate = ps.executeUpdate();
            if(executeUpdate==1){
                    CommonUtil.updateSyncState();
                    return true;
                }
                return false;
        }
}
